package com.sinopac.model.LogQuery;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import netfusion.jbranch.database.DataAccessManager;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.sinopac.entity.LogQuery.LogVO;
import com.sinopac.entity.LogQuery.LoginLogVO;
import com.sinopac.entity.LogQuery.VerifyLogVO;
import com.sinopac.model.core.ModelCore;
import com.sinopac.util.PropsUtil;

public class LogQueryModel extends ModelCore{
	SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");
	
	public String exportLog(List<LogVO> logList, String record) throws Exception {
		// New Excel Work Book
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		wb.setSheetName(0, record, HSSFCell.ENCODING_UTF_16);
		int rnum = 0;
		int cnum = 0;
		// Create a row and put some cells in it. Rows are 0 based.
		HSSFRow row = sheet.createRow((short) rnum++);
		// Create a cell and put a value in it.
		HSSFCell cell = row.createCell((short) cnum++);

		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("日期");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("客戶ID");

		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("TOKEN ESN");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("異動項目");

		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("異動結果");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("異動經辦");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("覆核主管");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("註解");
		
		if ((logList != null) && (logList.size() > 0)) {
			for (int i = 0; i < logList.size(); i++) {
				LogVO vo = logList.get(i);
				cnum = 0;
				row = sheet.createRow((short) rnum++);

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getLog_time());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getCustomer_id());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getToken_esn());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getJob_code());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getReturncode());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getOperator());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getManager());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getComment());
			}
			cnum = 0;
			row = sheet.createRow((short) rnum++);
			cell = row.createCell((short) cnum++);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("===== 總共 " + logList.size() + " 筆資料。");
		}

		// output Excel File
		String filePath = PropsUtil.getConfig("batchFile.path");
		File file = new File(filePath);
		if(!file.exists()){
			file.mkdirs();
		}
		StringBuffer dateStr = new StringBuffer(sdf.format(new Date()));
		String outputFile = filePath + record + dateStr + ".xls";
		FileOutputStream fOut = new FileOutputStream(outputFile);
		wb.write(fOut);
		fOut.flush();
		return outputFile;
	}
	
	/**
	 * 取得異動,申請,註銷畫面所需資料
	 * @param 
	 * @return
	 */
	public List<LogVO> getTokenRecord(String from_h, String to_h, String token_cust, String record){
		DataAccessManager dam = new DataAccessManager();
		List<LogVO> logList = new ArrayList<LogVO>();		
		Map<String,String> otpStatus = new HashMap<String, String>();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		otpStatus.put("1000", "配發TOKEN");
		otpStatus.put("1001", "啟用TOKEN");
		otpStatus.put("1002", "暫禁TOKEN");
		otpStatus.put("1003", "解禁TOKEN");
		otpStatus.put("1004", "註銷TOKEN");
//		otpStatus.put("1005", "同步TOKEN");
		try {
			StringBuffer sql = new StringBuffer();
			if("異動紀錄".equals(record)){
				sql.append("SELECT * FROM Logs WHERE job_code IN('1001','1002','1003') AND log_time BETWEEN :from_h AND :to_h");
				if(token_cust.length()>16){
					sql.append(" AND customer_id = '"+ token_cust +"'");
				}else if(token_cust.length()>0 && token_cust.length()<=16){
//					System.out.println(token_cust.length());
//					System.out.println(token_cust + "GG");
					sql.append(" AND token_esn = '"+ token_cust +"'");
				}
			}else if("申請紀錄".equals(record)){
				sql.append("SELECT * FROM Logs WHERE job_code = '1000' AND log_time BETWEEN :from_h AND :to_h");
				if(token_cust.length()>16){
					sql.append(" AND customer_id = '"+ token_cust +"'");
				}else if(token_cust.length()>0 && token_cust.length()<=16){
					sql.append(" AND token_esn = '"+ token_cust +"'");
				}
			}else if("註銷紀錄".equals(record)){
				sql.append("SELECT * FROM Logs WHERE job_code = '1004' AND log_time BETWEEN :from_h AND :to_h");
				if(token_cust.length()>16){
					sql.append(" AND customer_id = '"+ token_cust +"'");
				}else if(token_cust.length()>0 && token_cust.length()<=16){
					sql.append(" AND token_esn = '"+ token_cust +"'");
				}
			}
			dam.setObject("from_h", from_h + " 00:00:00");
			dam.setObject("to_h", to_h + " 23:59:59");
//			dam.setObject("token_cust", token_cust);
			dam.setQueryString(sql.toString());
			List<Map<String,Object>> list = dam.exeQuery();
			if(!list.isEmpty()){
				for(Map<String,Object> map:list){
					LogVO logVO = new LogVO();
//					System.out.println("customer_id=" + (String)map.get("customer_id"));
					logVO.setLog_time(map.get("log_time") != null?df.format((Timestamp)map.get("log_time")):"");
					logVO.setCustomer_id((String)map.get("customer_id"));
					logVO.setToken_esn((String)map.get("token_esn"));
					logVO.setJob_code(otpStatus.get((String)map.get("job_code")));
					logVO.setReturncode((String)map.get("returncode"));
					logVO.setOperator((String)map.get("operator"));
					logVO.setManager((String)map.get("manager"));
					logVO.setComment((String)map.get("comment"));
					logList.add(logVO);
				}				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return logList;		
	}
	
	public String exportVerify(List<VerifyLogVO> verifyLogList, String record) throws Exception {
		// New Excel Work Book
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		wb.setSheetName(0, record, HSSFCell.ENCODING_UTF_16);
		int rnum = 0;
		int cnum = 0;
		// Create a row and put some cells in it. Rows are 0 based.
		HSSFRow row = sheet.createRow((short) rnum++);
		// Create a cell and put a value in it.
		HSSFCell cell = row.createCell((short) cnum++);

		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("日期");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("客戶ID");

		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("TOKEN ESN");

		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("驗證結果");
		
		if ((verifyLogList != null) && (verifyLogList.size() > 0)) {
			for (int i = 0; i < verifyLogList.size(); i++) {
				VerifyLogVO vo = verifyLogList.get(i);
				cnum = 0;
				row = sheet.createRow((short) rnum++);

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getLog_time());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getCustomer_id());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getToken_esn());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getReturncode());
			}
			cnum = 0;
			row = sheet.createRow((short) rnum++);
			cell = row.createCell((short) cnum++);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("===== 總共 " + verifyLogList.size() + " 筆資料。");
		}

		// output Excel File
		String filePath = PropsUtil.getConfig("batchFile.path");
		File file = new File(filePath);
		if(!file.exists()){
			file.mkdirs();
		}
		StringBuffer dateStr = new StringBuffer(sdf.format(new Date()));
		String outputFile = filePath + record + dateStr + ".xls";
		FileOutputStream fOut = new FileOutputStream(outputFile);
		wb.write(fOut);
		fOut.flush();
		return outputFile;
	}
	
	/**
	 * 取得驗證畫面所需資料
	 * @param 
	 * @return
	 */
	public List<VerifyLogVO> getCheckRecord(String from_h, String to_h){
		DataAccessManager dam = new DataAccessManager();
		List<VerifyLogVO> verifyLogList = new ArrayList<VerifyLogVO>();
		Map<String,String> otpStatus = new HashMap<String, String>();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			String sql = "SELECT * FROM Verify_Logs WHERE job_code = '5000' AND log_time BETWEEN :from_h AND :to_h";
			dam.setObject("from_h", from_h + " 00:00:00");
			dam.setObject("to_h", to_h + " 23:59:59");
			dam.setQueryString(sql);
			List<Map<String,Object>> list = dam.exeQuery();
			if(!list.isEmpty()){
				for(Map<String,Object> map:list){
					VerifyLogVO verifyLogVO = new VerifyLogVO();
					verifyLogVO.setLog_time(map.get("log_time") != null?df.format((Timestamp)map.get("log_time")):"");
					verifyLogVO.setCustomer_id((String)map.get("customer_id"));
					verifyLogVO.setToken_esn((String)map.get("token_esn"));					
					verifyLogVO.setReturncode((String)map.get("returncode"));
					verifyLogList.add(verifyLogVO);
				}				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return verifyLogList;		
	}
	
	public String exportLogin(List<LoginLogVO> loginLogList, String record) throws Exception {
		// New Excel Work Book
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		wb.setSheetName(0, record, HSSFCell.ENCODING_UTF_16);
		int rnum = 0;
		int cnum = 0;
		// Create a row and put some cells in it. Rows are 0 based.
		HSSFRow row = sheet.createRow((short) rnum++);
		// Create a cell and put a value in it.
		HSSFCell cell = row.createCell((short) cnum++);

		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("使用者代碼");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("使用者名稱");

		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("使用者角色");

		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("登入來源");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("客戶端來源IP");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("紀錄時間");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("作業結果");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("註解");
		
		if ((loginLogList != null) && (loginLogList.size() > 0)) {
			for (int i = 0; i < loginLogList.size(); i++) {
				LoginLogVO vo = loginLogList.get(i);
				cnum = 0;
				row = sheet.createRow((short) rnum++);

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getUserID());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getUserName());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getRoleType());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getSource());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getHostIP());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getLogDateTime());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getResult());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getMemo());
			}
			cnum = 0;
			row = sheet.createRow((short) rnum++);
			cell = row.createCell((short) cnum++);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("===== 總共 " + loginLogList.size() + " 筆資料。");
		}

		// output Excel File
		String filePath = PropsUtil.getConfig("batchFile.path");
		File file = new File(filePath);
		if(!file.exists()){
			file.mkdirs();
		}
		StringBuffer dateStr = new StringBuffer(sdf.format(new Date()));
		String outputFile = filePath + record + dateStr + ".xls";
		FileOutputStream fOut = new FileOutputStream(outputFile);
		wb.write(fOut);
		fOut.flush();
		return outputFile;
	}
	
	/**
	 * 取得登入log畫面所需資料
	 * @param 
	 * @return
	 */
	public List<LoginLogVO> getLoginRecord(String from_h, String to_h){
		Map<String,String> roleType = new HashMap<String, String>();
		roleType.put("", "");
		roleType.put("1", "行員");
		roleType.put("2", "主管");
		roleType.put("3", "查詢");
		roleType.put("4", "前置");
		roleType.put("9", "系統管理者");
		Map<String,String> resultType = new HashMap<String, String>();
		resultType.put("10", "使用者不存在");
		resultType.put("14", "MD5驗證碼錯誤");
		resultType.put("01", "成功");
		resultType.put("13", "密碼錯誤");
		resultType.put("11", "角色權限不正確");
		String source = "";
		DataAccessManager dam = new DataAccessManager();
		List<LoginLogVO> loginLogList = new ArrayList<LoginLogVO>();
		Map<String,String> otpStatus = new HashMap<String, String>();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			String sql = "SELECT * FROM LoginLog WHERE LogDateTime BETWEEN :from_h AND :to_h";
			dam.setObject("from_h", from_h + " 00:00:00");
			dam.setObject("to_h", to_h + " 23:59:59");
			dam.setQueryString(sql);
			List<Map<String,Object>> list = dam.exeQuery();
			if(!list.isEmpty()){
				for(Map<String,Object> map:list){
					LoginLogVO loginLogVO = new LoginLogVO();
					loginLogVO.setUserID(map.get("UserID").toString());
					loginLogVO.setUserName(map.get("UserName").toString());
					loginLogVO.setRoleType(roleType.get(map.get("RoleType").toString()));
					if(map.get("Source").toString().equals("01")){
						source = "權限管理平台";
					}else{
						source = "分行平台";
					}
					loginLogVO.setSource(source);
					loginLogVO.setHostIP(map.get("HostIP").toString());
					loginLogVO.setLogDateTime(map.get("LogDateTime").toString());
					loginLogVO.setResult(resultType.get(map.get("Result").toString()));
					loginLogVO.setMemo(map.get("Memo").toString());
					loginLogList.add(loginLogVO);
				}				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return loginLogList;		
	}
}
